package com.me.dao.impl;

import com.me.dao.IUserDao;
import com.me.domain.User;
import com.me.util.DBHelper;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;


import java.sql.SQLException;
import java.util.List;

public class UserDao implements IUserDao {
    @Override
    public User getByName(String username) {
        try {
            QueryRunner runner = new QueryRunner(DBHelper.getDatasource());
            String sql = "select * from user where name=?";
            return runner.query(sql,new BeanHandler<User>(User.class),username);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    public List<User> searchCustomer(String customerName){
        try {
            QueryRunner runner = new QueryRunner(DBHelper.getDatasource());
            String sql = "select * from user u where u.name like ? and u.role='customer'";
            return runner.query(sql,new BeanListHandler<User>(User.class),"%" + customerName + "%");
        }catch (SQLException e){
            e.printStackTrace();
        }
        return null;
    }
    public User getById(int id) {
        try {
            QueryRunner runner = new QueryRunner(DBHelper.getDatasource());
            String sql = "select * from user u where u.id=?";
            return runner.query(sql,new BeanHandler<User>(User.class),id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    public void save(User user){
        try {
            QueryRunner runner = new QueryRunner(DBHelper.getDatasource());
            String sql = "INSERT INTO `pet`.`user` (`role`, `name`, `pwd`, `tel`, `address`) VALUES (?, ?, ?, ?, ?);";
            runner.update(sql,user.getRole(),user.getName(),user.getPwd(),user.getTel(),user.getAddress());
        }catch (SQLException e){
            e.printStackTrace();
        }
    }



    @Override
    public List<User> getAll() {
        try {
            QueryRunner runner = new QueryRunner(DBHelper.getDatasource());
            String sql = "select * from user ";
            return runner.query(sql,new BeanListHandler<User>(User.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public void update(User user) {
        try {
            QueryRunner runner = new QueryRunner(DBHelper.getDatasource());
            String sql = "UPDATE `pet`.`user` SET  `name` = ?,  `tel` = ?, `address` = ? WHERE (`id` = ?);";
            runner.update(sql,user.getName(),user.getTel(),user.getAddress(),user.getId());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void del(User user) {
        try {
            QueryRunner runner = new QueryRunner((DBHelper.getDatasource()));
            String sql = "DELETE FROM `pet`.`user` WHERE `id`=? and role!='admin';";
            runner.update(sql,user.getId());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void repwd(User user) {
        try {
            QueryRunner runner = new QueryRunner(DBHelper.getDatasource());
            String sql = "UPDATE `pet`.`user` SET  `pwd` = ? WHERE (`id` = ?);";
            runner.update(sql,user.getPwd(),user.getId());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}
